﻿namespace Dcms.BaseLib
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;

    public class SqlHelper
    {
        private static readonly string CONN_STRING = ConfigurationManager.AppSettings["CONN_STRING"];

        public static DataSet CreateDataSet(string sql)
        {
            DataSet dataSet = new DataSet();
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                connection.Open();
                new SqlDataAdapter(sql, connection).Fill(dataSet);
            }
            return dataSet;
        }

        public static DataSet CreateDataSet(string SQLString, params SqlParameter[] cmdParms)
        {
            DataSet set2;
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        adapter.Fill(dataSet, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException exception)
                    {
                        throw new Exception(exception.Message);
                    }
                    set2 = dataSet;
                }
            }
            return set2;
        }

        public static DataSet CreateDataSet(string sql, int Begin, int ShowCount)
        {
            DataSet dataSet = new DataSet();
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, Begin, ShowCount, "table");
            }
            return dataSet;
        }

        public static DataSet CreatePaginationDataSet(string sql, int ShowCount, int PageNumber)
        {
            DataSet dataSet = new DataSet();
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, ShowCount * PageNumber, ShowCount, "Revert");
            }
            return dataSet;
        }

        public static DataTable DataTableList(string cmdText, CommandType cmdType, int ShowCount, int PageNumber)
        {
            DataTable table = new DataTable();
            using (SqlCommand command = new SqlCommand())
            {
                using (SqlConnection connection = new SqlConnection(CONN_STRING))
                {
                    PrepareCommand(command, connection, null, cmdType, cmdText, null);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    int startRecord = ShowCount * (PageNumber - 1);
                    adapter.Fill(startRecord, ShowCount, new DataTable[] { table });
                }
            }
            return table;
        }

        public static DataTable DataTableList(string cmdText, CommandType cmdType, int ShowCount, int PageNumber, params SqlParameter[] commandParameters)
        {
            DataTable table = new DataTable();
            using (SqlCommand command = new SqlCommand())
            {
                using (SqlConnection connection = new SqlConnection(CONN_STRING))
                {
                    PrepareCommand(command, connection, null, cmdType, cmdText, commandParameters);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    int startRecord = ShowCount * (PageNumber - 1);
                    adapter.Fill(startRecord, ShowCount, new DataTable[] { table });
                }
            }
            return table;
        }

        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return reader;
            }
        }

        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object obj2 = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj2;
            }
        }

        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            int num2;
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    int num = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return num;
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    if (cmd != null) cmd.Dispose();
                }
            }
            return num2;
        }

        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        foreach (DictionaryEntry entry in SQLStringList)
                        {
                            string cmdText = entry.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[]) entry.Value;
                            PrepareCommand(cmd, connection, transaction, cmdText, cmdParms);
                            int num = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            transaction.Commit();
                        }
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public static DataTable ExecuteTable(string cmdText, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            DataTable table = new DataTable();
            using (SqlCommand command = new SqlCommand())
            {
                using (SqlConnection connection = new SqlConnection(CONN_STRING))
                {
                    PrepareCommand(command, connection, null, cmdType, cmdText, commandParameters);
                    using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        table.Load(reader);
                    }
                    return table;
                }
            }
        }

        public static int GetDataCount(string cmdText, int DefaultCount)
        {
            object obj2 = ExecuteScalar(CONN_STRING, CommandType.Text, cmdText, null);
            if (obj2 == null || obj2.ToString() == "") return DefaultCount;
            return Convert.ToInt32(obj2);
        }

        public static int GetDataCount(string cmdText, int DefaultCount, params SqlParameter[] commandParameters)
        {
            object obj2 = ExecuteScalar(CONN_STRING, CommandType.Text, cmdText, commandParameters);
            if (obj2 == null || obj2.ToString() == "") return DefaultCount;
            return Convert.ToInt32(obj2);
        }

        public static string GetMaxNoteID(string FileName, string TableName)
        {
            string str = "";
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                connection.Open();
                SqlDataReader reader = new SqlCommand(string.Format("select max({0}) from {1}", FileName, TableName), connection).ExecuteReader();
                if (reader.Read()) str = reader[0].ToString();
            }
            return str;
        }

        public static int GetNoteCount(string sql)
        {
            int num = 0;
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                connection.Open();
                SqlDataReader reader = new SqlCommand(sql, connection).ExecuteReader();
                if (reader.Read()) num = Convert.ToInt32(reader[0]);
            }
            return num;
        }

        public static int GetNoteCount(string TableName, string FieldName, string FieldValue)
        {
            int num = 0;
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                connection.Open();
                SqlDataReader reader = new SqlCommand(string.Format("select count(*) from {0} where {1}='{2}'", TableName, FieldName, FieldValue), connection).ExecuteReader();
                if (reader.Read()) num = Convert.ToInt32(reader[0]);
            }
            return num;
        }

        public static string GetOneFileValue(string cmdText, string DefaultStr)
        {
            object obj2 = ExecuteScalar(CONN_STRING, CommandType.Text, cmdText, null);
            if (obj2 == null || obj2.ToString() == "") return DefaultStr;
            return obj2.ToString();
        }

        public static string GetOneFileValue(string cmdText, string DefaultStr, params SqlParameter[] commandParameters)
        {
            object obj2 = ExecuteScalar(CONN_STRING, CommandType.Text, cmdText, commandParameters);
            if (obj2 == null || obj2.ToString() == "") return DefaultStr;
            return obj2.ToString();
        }

        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            object obj3;
            using (SqlConnection connection = new SqlConnection(CONN_STRING))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    object objA = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value)) return null;
                    return objA;
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    if (cmd != null) cmd.Dispose();
                }
            }
            return obj3;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null) cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null) parameter.Value = DBNull.Value;
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null) cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }
}

